package com.bringspring.visualdev.onlinedev.util;

import com.bringspring.common.base.ActionResult;
import com.bringspring.common.constant.MsgCode;
import com.bringspring.common.database.model.dto.PreparedStatementDTO;
import com.bringspring.common.database.model.entity.DbLinkEntity;
import com.bringspring.common.exception.DataException;
import com.bringspring.common.model.visiual.TableModel;
import com.bringspring.common.util.JsonUtil;
import com.bringspring.visualdev.base.entity.VisualdevEntity;
import com.bringspring.visualdev.base.util.VisualUtils;
import lombok.Cleanup;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 处理在线开发新增更新操作
 * @author 开发平台组
 * @version V1.0.0
 * @copyright 股份有限公司
 * @date 2021年3月22日10:58:29
 */
public class OnlineDevDbUtil {

    /**
     * 删除有表单条数据
     * @param id
     * @param visualdevEntity
     * @return
     * @throws SQLException
     * @throws DataException
     */
    public static boolean deleteTable(String id, VisualdevEntity visualdevEntity, DbLinkEntity linkEntity) throws SQLException, DataException {
        List<TableModel> tableModels = JsonUtil.getJsonToList(visualdevEntity.getVisualTables(), TableModel.class);
        //主表
        TableModel mainTableModel = tableModels.stream().filter(t -> "1".equals(t.getTypeId())).findFirst().orElse(null);
        String mainTable = mainTableModel.getTable();
        @Cleanup Connection conn = VisualUtils.getDataConn(linkEntity);
        //获取主键
        String pKeyName = VisualUtils.getpKey(conn, mainTable);

        String delMain = "DELETE FROM " + mainTable + " WHERE " + pKeyName + "= ?";
        List<PreparedStatementDTO> DTOs = new LinkedList<>();
        PreparedStatementDTO dto = new PreparedStatementDTO(conn,delMain,id);
        DTOs.add(dto);

        String queryMain = "SELECT * FROM" + " " + mainTable + " WHERE " + pKeyName + "='" + id + "'";
        List<Map<String, Object>> mainMapList = VisualUtils.getTableDataInfo(queryMain,linkEntity);
        mainMapList = VisualUtils.toLowerKeyList(mainMapList);
        if (mainMapList.size() > 0) {
            if (tableModels.size() > 1) {
                //去除主表
                tableModels.remove(mainTableModel);
                for (TableModel table : tableModels) {
                    //主表字段
                    String relationField = table.getRelationField();
                    String relationFieldValue = mainMapList.get(0).get(relationField.toLowerCase()).toString();
                    //子表字段
                    String tableField = table.getTableField();
                    String childSql = "DELETE FROM " + table.getTable() + " WHERE " + tableField + "= ?";
                    PreparedStatementDTO childDto = new PreparedStatementDTO(conn,childSql,relationFieldValue);
                    DTOs.add(childDto);
                }
            }
        }
        return sqlPrepareStatement(conn, DTOs,true);
    }

    /**
     * 删除有表多条数据
     * @param idList
     * @param visualdevEntity
     * @return
     * @throws SQLException
     * @throws DataException
     */
    public static ActionResult deleteTables(List<String> idList, VisualdevEntity visualdevEntity, DbLinkEntity linkEntity) throws SQLException, DataException {
        List<TableModel> tableModels = JsonUtil.getJsonToList(visualdevEntity.getVisualTables(),TableModel.class);

        if (idList.size()==0){
            return ActionResult.fail(MsgCode.FA003.get());
        }

        TableModel tableModel = tableModels.stream().filter(t -> "1".equals(t.getTypeId())).findFirst().orElse(null);
        //取主表
        String mainTable = Optional.ofNullable(tableModel.getTable()).orElse("") ;
        //判断数据源
        @Cleanup Connection conn = VisualUtils.getDataConn(linkEntity);
        //获取主键
        String pKeyName = VisualUtils.getpKey(conn, mainTable);

        //批量删除 关闭链接按钮
        boolean close = false;

        //查询数据是否存在
            for (int i =0;i<idList.size();i++){
             String id = idList.get(i);
            String selectSql= "SELECT * FROM " + mainTable + " WHERE " + pKeyName + "='" + id + "'";
            List<Map<String, Object>> mainMapList = VisualUtils.getTableDataInfo(selectSql,linkEntity);

            String delMain = "DELETE FROM " + mainTable + " WHERE " + pKeyName + "= ?";
            List<PreparedStatementDTO> DTOs = new LinkedList<>();
            PreparedStatementDTO dto = new PreparedStatementDTO(conn,delMain,id);
            DTOs.add(dto);

            if (mainMapList.size()>0){
                //是否存在子表
                if(tableModels.size()>1){
                    //去除主表
                    tableModels.remove(tableModel);
                    for (TableModel table : tableModels) {
                        //主表字段
                        String relationField = table.getRelationField();
                        String relationFieldValue = mainMapList.get(0).get(relationField.toLowerCase()).toString();
                        //子表字段
                        String tableField = table.getTableField();
                        String childSql = "DELETE FROM " + table.getTable() + " WHERE " + tableField + "= ?";
                        PreparedStatementDTO childDto = new PreparedStatementDTO(conn,childSql,relationFieldValue);
                        DTOs.add(childDto);
                    }
                }
            }
            if (i==idList.size()-1){
                close = true;
            }
            sqlPrepareStatement(conn, DTOs, close);
        }
        if (conn!=null){
            conn.close();
        }
        return ActionResult.success(MsgCode.SU003.get());
    }

    /**
     * 删除
     * @param conn
     * @param DTOs
     * @param close
     * @return
     */
    public static Boolean sqlPrepareStatement(Connection conn, List<PreparedStatementDTO> DTOs, boolean close) {
        try {
            //手动
            conn.setAutoCommit(false);
            for (PreparedStatementDTO DTO : DTOs){
                @Cleanup PreparedStatement statement = conn.prepareStatement(DTO.getPrepareSql());
                LinkedList<Object> prepareDataList = DTO.getPrepareDataList();
                for (int i =0;i<prepareDataList.size();i++){
                    statement.setObject(i+1,prepareDataList.get(i));
                }
                 statement.executeUpdate();
            }
                conn.commit();
        } catch (SQLException sqlException) {
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            sqlException.printStackTrace();
        } finally {
            if (close){
                if (conn!=null){
                    try {
                        conn.close();
                    } catch (SQLException sqlException) {
                        sqlException.printStackTrace();
                    }
                }
            }
        }
        return true;
    }
}
